package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import ModelLayer.Room;
import ModelLayer.RoomBooking;
import ModelLayer.RoomBookingLine;
import ModelLayer.RoomType;

public class DBRBLine implements IFDBRBLine {
	private Connection con;

	public DBRBLine() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public RoomBookingLine getLine(int rbId, int lineId,
			boolean retrieveAssociation) {
		String wClause = "  rb_Id = '" + rbId + "'" + " AND lineId = '"
				+ lineId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertLine(RoomBookingLine line) {
		int rc = -1;
		int nextId = GetMaxId
				.getMaxId("Select max(lineId) From roomBookingLine") + 1;
		String query = "INSERT INTO roomBookingLine(rb_Id, lineId, rt_Id, quantity, totalPrice) VALUES('"
				+ line.getRoomBooking().getId()
				+ "','"
				+ nextId
				+ "','"
				+ line.getRoomType().getId()
				+ "','"
				+ line.getQuantity()
				+ "','" + line.getTotalPrice() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("RBLine is not inserted");
		}
		return nextId;
	}

	@Override
	public LinkedList<RoomBookingLine> getAllLineForRB(int rbId,
			boolean retrieveAssociation) {
		String wClause = "  rb_Id = '" + rbId + "'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public int updateLine(RoomBookingLine line) {
		RoomBookingLine lineObj = line;
		int rc = -1;
		String query = "UPDATE roomBookingLine SET" + " rt_Id = '"
				+ line.getRoomType().getId() + "'," + " quantity = '"
				+ line.getQuantity() + "'," + "totalPrice = '"
				+ line.getTotalPrice() + "'" + " WHERE rb_Id = '"
				+ lineObj.getRoomBooking().getId() + "'" + " AND lineId = '"
				+ lineObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in RBline db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteLinesForRB(int rbId) {
		int rc = -1;

		String query = "DELETE FROM roomBookingLine WHERE rb_Id = '" + rbId
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in RBline db: " + ex);
		}
		return (rc);
	}

	private RoomBookingLine singleWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		RoomBookingLine line = new RoomBookingLine();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				line = buildRBLine(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBRoomBooking dbRB = new DBRoomBooking();
					RoomBooking rb = dbRB.getRoomBooking(line.getRoomBooking()
							.getId(), false);
					line.setRoomBooking(rb);
					System.out.println("room booking is selected");

					IFDBRoomType dbRT = new DBRoomType();
					RoomType rt = dbRT.getRoomType(line.getRoomType().getId());
					line.setRoomType(rt);
					System.out.println("room type is selected");

					IFDBRoomBookingLine_Room dbRbl_Room = new DBRoomBookingLine_Room();
					LinkedList<Room> rooms = dbRbl_Room.getAllRoomForRBL(line
							.getId());
					line.setRooms(rooms);
					System.out.println("rooms are selected");
				}
			} else {
				line = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return line;
	}

	private LinkedList<RoomBookingLine> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		LinkedList<RoomBookingLine> list = new LinkedList<RoomBookingLine>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				RoomBookingLine line = new RoomBookingLine();
				line = buildRBLine(results);
				list.add(line);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (RoomBookingLine line : list) {
					IFDBRoomBooking dbRB = new DBRoomBooking();
					RoomBooking rb = dbRB.getRoomBooking(line.getRoomBooking()
							.getId(), false);
					line.setRoomBooking(rb);
					System.out.println("room booking is selected");

					IFDBRoomType dbRT = new DBRoomType();
					RoomType rt = dbRT.getRoomType(line.getRoomType().getId());
					line.setRoomType(rt);
					System.out.println("room type is selected");

					IFDBRoomBookingLine_Room dbRbl_Room = new DBRoomBookingLine_Room();
					LinkedList<Room> rooms = dbRbl_Room.getAllRoomForRBL(line
							.getId());
					line.setRooms(rooms);
					System.out.println("rooms are selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private RoomBookingLine buildRBLine(ResultSet results) {
		RoomBookingLine line = new RoomBookingLine();
		try {
			line.setQuantity(results.getInt("quantity"));
			line.setRoomBooking(new RoomBooking(results.getInt("rb_Id")));
			line.setRoomType(new RoomType(results.getInt("rt_Id")));
			line.setId(results.getInt("lineId"));
			line.setTotalPrice(results.getDouble("totalPrice"));
		} catch (Exception e) {
			System.out.println("error in building the roomBookingLine object");
		}
		return line;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT rb_Id, lineId, rt_Id, quantity, totalPrice FROM roomBookingLine";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
